package com.soft.blog_project.controller;

import com.soft.blog_project.pojo.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

@Api(tags = "博客模块")
@RestController
@CrossOrigin(origins="*")
public class BlogController {
    @Autowired(required=false)//初始化对象(这一句可以解决17行jdbc红色)
    private JdbcTemplate jdbc;

    //评论展示
    // http://localhost:9090/blogComment?blog_id=1
    @ApiOperation(value = "根据Blog_id显示博客评论",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/blogComment")
    public Result blogComment(@ApiParam(value = "博客ID", required = true)Integer blog_id){
        Result result=new Result();
        try {
            List<ShowComment> blogcom=jdbc.query("SELECT User_id ID,user_name Name,comment_txt Text,user_img image,city City from comment natural join user where Blog_id=?",
                    new BeanPropertyRowMapper<>(ShowComment.class),blog_id);
            result.setCode(200);
            result.setResult(blogcom);
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("Failed"+e.getMessage());
        }
        return result;
    }

    //添加评论
    @ApiOperation(value = "添加评论",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示发布评论成功，201表示发布评论异常")
    @GetMapping ("/AddComment")
    //http://localhost:9090/AddComment
    public Result AddComment(@ApiParam(value = "User_iD", required = true)Integer userid,
                             @ApiParam(value = "Blog_iD", required = true)Integer blogid ,
                             @ApiParam(value = "comment_txt", required = true)String commenttxt){
        Result result= new Result();
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String formattedTime = currentTime.format(formatter);
        try {
            jdbc.update("insert into Comment(User_id,Blog_id,comment_txt,comment_time) values(?,?,?,?) ",
                    userid,blogid,commenttxt,formattedTime);
            result.setCode(200);
            result.setResult("发布评论成功！");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("评论异常"+e.getMessage());
            return result;
        }
    }

    //判断是否点赞
    //http://localhost:9090/judgeLike?user_id=1&blog_id=1
    @ApiOperation(value = "判断是否点赞",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID查询点赞状态,code:2011表示已点赞，200表示未点赞，201表示点赞异常，400表示用户id或博客id为空")
    @GetMapping("/judgeLike")
    public Result judgeLike(@ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                            @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        if(user_id!=null&&blog_id!=null){
            try{
                Integer temp = jdbc.queryForObject("SELECT favor FROM user_blog WHERE User_id=? AND Blog_id=?", new Object[]{user_id, blog_id}, Integer.class);
                if(temp==1){
                    result.setCode(2011);
                    result.setResult("已点赞");
                }
                else if(temp==0) {
                    result.setCode(200);
                    result.setResult("未点赞");
                }
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("点赞异常"+e.getMessage());
            }
        }else{//用户id和博客id为空
            result.setCode(400);
            result.setResult("请求参数错误：user_id或blog_id不能为空");
        }
        return result;
    }


    //点赞
    //http://localhost:9090/ClickLike?user_id=1&blog_id=1
    @ApiOperation(value = "点赞操作",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID执行点赞操作,code:200表示点赞成功，201表示点赞异常")
    @GetMapping("/ClickLike")
    public Result ClickLike( @ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                             @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        Integer tmp1=jdbc.queryForObject("select count(*) from user_blog where User_id=? and Blog_id=?",Integer.class,user_id,blog_id);
        if(tmp1==0){
            try{
                jdbc.update("insert into user_blog(Blog_id,User_id,favor,collect,comments) values (?,?,?,?,?)",blog_id,user_id,1,0,0);
                result.setCode(200);
                result.setResult("首次点赞成功！");
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("点赞异常"+e.getMessage());
            }
        }else{
            Integer tmp2=jdbc.queryForObject("SELECT favor FROM user_blog WHERE User_id=? AND Blog_id=?", Integer.class,user_id,blog_id);
            if(tmp2==0){
                try{
                    jdbc.update("update user_blog set favor=1 where User_id=? and Blog_id=?",user_id,blog_id);
                    result.setCode(200);
                    result.setResult("点赞成功！");
                }catch (DataAccessException e) {
                    e.printStackTrace();
                    result.setCode(201);
                    result.setResult("点赞异常"+e.getMessage());
                }
            }
        }
        return result;
    }

    //取消点赞
    //http://localhost:9090/notLike?user_id=1&blog_id=1
    @ApiOperation(value = "取消点赞",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID取消点赞,code:200表示取消点赞成功，201表示取消点赞异常，400表示用户id或博客id为空")
    @GetMapping("/notLike")
    public Result notLike( @ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                           @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        if(user_id!=null&&blog_id!=null){
            try{
                Integer temp = jdbc.queryForObject("SELECT favor FROM user_blog WHERE User_id=? AND Blog_id=?", new Object[]{user_id, blog_id}, Integer.class);
                if(temp==1){
                    jdbc.update("update user_blog set favor=0 where User_id=? and Blog_id=?",user_id,blog_id);
                    result.setCode(200);
                    result.setResult("取消点赞成功！");
                }
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("点赞异常"+e.getMessage());
            }
        }else{//用户id和博客id为空
            result.setCode(400);
            result.setResult("请求参数错误：user_id或blog_id不能为空");
        }
        return result;
    }

    //判断收藏
    //http://localhost:9090/ClickCollect?user_id=1&blog_id=1
    @ApiOperation(value = "判断是否收藏",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID查询收藏状态,code:2011表示已收藏，200表示未收藏，201表示收藏异常，400表示用户id或博客id为空")
    @GetMapping("/judgeCollect")
    public Result judgeCollect(@ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                               @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        if(user_id!=null&&blog_id!=null){
            try{
                Integer temp = jdbc.queryForObject("SELECT collect FROM user_blog WHERE User_id=? AND Blog_id=?", new Object[]{user_id, blog_id}, Integer.class);
                if(temp==1){
                    result.setCode(2011);
                    result.setResult("已收藏");
                }
                else if(temp==0) {
                    result.setCode(200);
                    result.setResult("未收藏！");
                }
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("收藏异常"+e.getMessage());
            }
        }else{//用户id和博客id为空
            result.setCode(400);
            result.setResult("请求参数错误：user_id或blog_id不能为空");
        }
        return result;
    }

    //收藏
    //http://localhost:9090/ClickCollect?user_id=1&blog_id=1
    @ApiOperation(value = "收藏操作",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID执行收藏操作,code:200表示收藏成功，201表示收藏异常")
    @GetMapping("/ClickCollect")
    public Result ClickCollect(@ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                               @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        Integer tmp1=jdbc.queryForObject("select count(*) from user_blog where User_id=? and Blog_id=?",Integer.class,user_id,blog_id);
        if(tmp1==0){
            try{
                jdbc.update("insert into user_blog(Blog_id,User_id,favor,collect,comments) values (?,?,?,?,?)",blog_id,user_id,0,1,0);
                result.setCode(200);
                result.setResult("首次收藏成功！");
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("收藏异常"+e.getMessage());
            }
        }else{
            Integer tmp2=jdbc.queryForObject("SELECT collect FROM user_blog WHERE User_id=? AND Blog_id=?", Integer.class,user_id,blog_id);
            if(tmp2==0){
                try{
                    jdbc.update("update user_blog set collect=1 where User_id=? and Blog_id=?",user_id,blog_id);
                    result.setCode(200);
                    result.setResult("收藏成功！");
                }catch (DataAccessException e) {
                    e.printStackTrace();
                    result.setCode(201);
                    result.setResult("收藏异常"+e.getMessage());
                }
            }
        }
        return result;
    }

    //取消收藏
    //http://localhost:9090/ClickCollect?user_id=1&blog_id=1
    @ApiOperation(value = "取消收藏",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID和博客ID取消收藏,code:200表示取消收藏成功，201表示取消收藏异常，400表示用户id或博客id为空")
    @GetMapping("/notCollect")
    public Result notCollect(@ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id,
                             @ApiParam(name = "blog_id", value = "博客ID", required = true) Integer blog_id){
        Result result=new Result();
        if(user_id!=null&&blog_id!=null){
            try{
                Integer temp = jdbc.queryForObject("SELECT collect FROM user_blog WHERE User_id=? AND Blog_id=?", new Object[]{user_id, blog_id}, Integer.class);
                if(temp==1){
                    jdbc.update("update user_blog set collect=0 where User_id=? and Blog_id=?",user_id,blog_id);
                    result.setCode(200);
                    result.setResult("取消收藏成功！");
                }
            }catch (DataAccessException e) {
                e.printStackTrace();
                result.setCode(201);
                result.setResult("收藏异常"+e.getMessage());
            }
        }else{//用户id和博客id为空
            result.setCode(400);
            result.setResult("请求参数错误：user_id或blog_id不能为空");
        }
        return result;
    }

    //我的收藏
    //http://localhost:9090/myCollect?user_id=1
    @ApiOperation(value = "我的收藏",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "根据用户ID展示该用户的收藏,code:200表示成功，201表示异常")
    @GetMapping ("/myCollect")
    public Result myCollect(@ApiParam(name = "user_id", value = "用户ID", required = true)Integer user_id){
        Result result=new Result();
        try {
            List<BlogAndUser> mycollect = jdbc.query("SELECT * FROM blog left join (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum\n" +
                    "from user_blog group by Blog_id) as ub USING (Blog_id)\n" +
                    "natural join (select Blog_id from user_blog where User_id = ? and collect = 1) as cb\n" +
                    "natural join user;", new BeanPropertyRowMapper<>(BlogAndUser.class),user_id);
            result.setCode(200);
            result.setResult(mycollect);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("收藏博客异常"+e.getMessage());
            return result;
        }
    }

    //搜索博客（根据标题或内容）
    //http://localhost:8088/BlogByContent?Content=222
    @ApiOperation(value = "搜索博客（根据标题或内容）",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/BlogByContent")
    public Result QueryBlogByContent(String Content){
        Result result=new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.query("SELECT * FROM blog left JOIN\n" +
                                    "    (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum  from user_blog\n" +
                                    "    group by Blog_id) as ub using(Blog_id) NATURAL JOIN user where title like '%' ? '%'  or content  like '%' ? '%'  order by favornum desc;",
                            new BeanPropertyRowMapper<>(BlogAndUser.class),Content,Content));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //搜索博客（根据标签类别）
    //http://localhost:8088/BlogByTypeId?TypeId=2
    @ApiOperation(value = "搜索博客（根据标签类别）",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/BlogByTypeId")
    public Result QueryBlogByType(Integer TypeId){
        Result result=new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.query("SELECT * FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum,sum(comments) as commentsnum  from user_blog group by Blog_id) as ub using (Blog_id)\n" +
                                    "    NATURAL JOIN user where type_id=? order by favornum desc limit 20;",
                            new BeanPropertyRowMapper<>(BlogAndUser.class),TypeId));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //搜索博客（根据地区）
    //http://localhost:8088/BlogByCity?City=重庆市
    @ApiOperation(value = "搜索博客（根据地区）",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/BlogByCity")
    public Result QueryBlogByCity(String City){
        Result result=new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.query("SELECT * FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum,sum(comments) as commentsnum  from user_blog group by Blog_id) as ub using(Blog_id)  NATURAL JOIN user where city= ? order by favornum desc limit 10;",
                            new BeanPropertyRowMapper<>(BlogAndUser.class),City));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //发布博客
    //http://localhost:9090/PostBlog
    @ApiOperation(value = "发布博客",
            protocols = "http",
            httpMethod = "POST",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @PostMapping("/PostBlog")
    public Result PostBlog(@RequestBody Blog blog){
        System.out.println(blog);
        Result result= new Result();
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String formattedTime = currentTime.format(formatter);
        try {
            jdbc.update("insert into blog(type_id, title, content, time,blog_img,user_id)\n"+
                    " values(?,?,?,?,?,?)",blog.getTypeId(),blog.getTitle(),blog.getContent(),formattedTime,blog.getBlogImg(),blog.getUserId());
            result.setCode(200);
            result.setResult(jdbc.queryForObject("SELECT Blog_id FROM blog where title=?;",Integer.class,blog.getTitle()));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }

    }

    //我的发布,登录之后已有用户名，根据user_id查看本人发布的博客
    //http://localhost:9090/MyBlog?UserId=1
    @ApiOperation(value = "根据user_id查看本人发布的博客",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/MyBlog")
    public Result LookMyBlog(Integer UserId){
        Result result=new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.query("SELECT * FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum,sum(comments) as commentsnum  from user_blog group by Blog_id) as ub \n" +
                                    "using (Blog_id)    NATURAL JOIN user where user_id=? order by favornum desc;",
                            new BeanPropertyRowMapper<>(BlogAndUser.class),UserId));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //删除我的博客
    //http://localhost:9090/DeleteMyBlog?UserId=2,Title=1347
    @ApiOperation(value = "删除我的博客",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/DeleteMyBlog")
    public Result DeleteMyBlog(Integer UserId,String Title){
        Result result=new Result();
        try {
            jdbc.update("delete from blog where user_id=? and title=? ;", UserId,Title);
            result.setCode(200);
            result.setResult("删除博客成功");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //修改我的博客（内容）
    //http://localhost:9090/UpdateMyBlogContent?NewContent=新内容&UserId=12&Title=问题一次应用软件.
    @ApiOperation(value = "修改我的博客（内容）",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/UpdateMyBlogContent")
    public Result UpdateMyBlogContent(String NewContent,Integer UserId,String Title){
        Result result=new Result();
        try {
            jdbc.update("update blog set content=? where user_id=? and title=? ;",NewContent,UserId,Title);
            result.setCode(200);
            result.setResult("修改博客内容成功");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //修改我的博客（标题）
    //http://localhost:9090/UpdateMyBlogTitle?NewTitle=这是新标题&UserId=104&Title=最后也是显示质量.
    @ApiOperation(value = "修改我的博客（标题）",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/UpdateMyBlogTitle")
    public Result UpdateMyBlogTitle(String NewTitle,Integer UserId,String Title){
        Result result=new Result();
        try {
            jdbc.update("update blog set title=? where user_id=? and title=? ;",NewTitle,UserId,Title);
            result.setCode(200);
            result.setResult("修改博客标题成功");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //获取消息列表
    //http://localhost:9090/message
    @GetMapping("/message")
    public Result Getmessage(Integer userId){
        Result result=new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.query("select message.* ,user.user_name,user.user_img from message join user\n" +
                            "on  message.other_id = user.user_id where message.user_id=?;",new BeanPropertyRowMapper<>(Message.class),userId)
            );
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("失败"+e.getMessage());
        }
        return result;
    }

    @ApiOperation(value = "博客内容显示",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功显示，201表示显示异常")
    @GetMapping("/showBlog")
    public Result showBlog(){
        Result result = new Result();
        try {
            List<BlogAndUser> blogAndUserList = jdbc.query("SELECT * FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum\n" +
                    "from user_blog group by Blog_id) as ub using(Blog_id)\n" +
                    "NATURAL JOIN user order by favornum desc\n" +
                    "limit 30;", new BeanPropertyRowMapper<>(BlogAndUser.class));
            result.setCode(200);
            result.setResult(blogAndUserList);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }

    }

    @GetMapping("/showBlog2")
    public Result showBlog2(){
        Result result = new Result();
        try {
            List<BlogAndUser> blogAndUserList = jdbc.query("select * from user natural join (\n" +
                    "    SELECT Blog_id,type_id,title,content,time,blog_img,blog.User_id,favornum,collectnum,commentsnum\n" +
                    "    FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum\n" +
                    "                         from user_blog group by Blog_id) as ub using(Blog_id)\n" +
                    "              left join (select Blog_id,count(*) as commentsnum from comment group by Blog_id) as bc\n" +
                    "                        using (Blog_id)\n" +
                    ") as ubc order by favornum desc limit 30;", new BeanPropertyRowMapper<>(BlogAndUser.class));
            result.setCode(200);
            result.setResult(blogAndUserList);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }

    }

    @ApiOperation(value = "显示关注用户的博客",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示显示成功，201表示显示异常")
    @GetMapping("/userFollowBlog")
    public Result userFollowBlog(@ApiParam(name = "userId", value = "用户ID", required = true)Integer userId){
        Result result = new Result();
        try {
            List<BlogAndUser> followBlogList = jdbc.query("SELECT * FROM blog left join (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum\n" +
                    "from user_blog group by Blog_id) as ub USING (Blog_id)\n" +
                    "NATURAL JOIN user\n" +
                    "join (select follow_id from follow where user_id = ?) as f\n" +
                    "where user.User_id = f.follow_id\n" +
                    "order by favornum desc;", new BeanPropertyRowMapper<>(BlogAndUser.class), userId);
            result.setCode(200);
            result.setResult(followBlogList);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }
    }

    //博客详情页面
    @ApiOperation(value = "博客详情页面显示",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功显示，201表示显示异常")
    @GetMapping("/showBlogDetail")
    public Result showBlogDetail(@ApiParam(name = "blogId", value = "博客ID", required = true)Integer blogId){
        Result result = new Result();
        try {
            List<BlogAndUser2> blogDetail = jdbc.query("select * from user natural join (\n" +
                    "    SELECT Blog_id,type_id,title,content,time,blog_img,blog.User_id,favornum,collectnum,count(comment.comment_id) as commentsnum\n" +
                    "    FROM blog NATURAL JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum\n" +
                    "                            from user_blog group by Blog_id) as ub\n" +
                    "              left join comment using (Blog_id)\n" +
                    "    where Blog_id=?\n" +
                    "    ) as ubc;", new BeanPropertyRowMapper<>(BlogAndUser2.class), blogId);
            result.setCode(200);
            result.setResult(blogDetail);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }

    }

    @ApiOperation(value = "浏览量",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/lookNum")
    public Result lookNum(){
        Result result = new Result();
        try {
            Integer looknum = jdbc.queryForObject("select sum(ifnull(favornum,0)+ifnull(collectnum,0)+ifnull(commentsnum,0)) as looknum \n" +
                            "from (select sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum from user_blog) as look;",
                    Integer.class);
            result.setCode(200);
            result.setResult(looknum);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }
    }

    @ApiOperation(value = "获取评论数量",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/userLookBlog")
    private Result userLookBlog(@ApiParam(name = "uuserId", value = "用户ID", required = true)Integer uuserId){
        Result result = new Result();
        try {
            List<BlogAndUser> userLookBlogs = jdbc.query("SELECT * FROM blog left JOIN (select Blog_id, sum(favor) as favornum, sum(collect) as collectnum, sum(comments) as commentsnum\n" +
                    "from user_blog group by Blog_id) as ub using(Blog_id)\n" +
                    "left join look using (Blog_id)\n" +
                    "natural join user\n" +
                    "where look.uuser_id = ?\n" +
                    "order by look_id desc;", new BeanPropertyRowMapper<>(BlogAndUser.class), uuserId);
            result.setCode(200);
            result.setResult(userLookBlogs);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }
    }

    @ApiOperation(value = "浏览记录",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/userLook")
    public Result userLook(@ApiParam(name = "uuserId", value = "用户ID", required = true)Integer uuserId,
                           @ApiParam(name = "blogId", value = "博客ID", required = true)Integer blogId){
        Result result = new Result();
        try {
            Integer lookOrNot = jdbc.queryForObject("select count(*) from look where uuser_id = ? and blog_id = ?;", Integer.class,uuserId,blogId);
            if(lookOrNot == 1){
                try {
                    jdbc.update("delete from look where uuser_id = ? and blog_id = ?;",uuserId,blogId);
                    jdbc.update("insert into look(uuser_id, blog_id) VALUE (?,?);",uuserId,blogId);
                    result.setCode(200);
                    result.setResult("记录更新成功！");
                    return result;
                } catch (DataAccessException e) {
                    e.printStackTrace();
                    result.setCode(201);
                    result.setResult("异常！记录更新失败"+e.getMessage());
                    return result;
                }
            }else{
                try {
                    jdbc.update("insert into look(uuser_id, blog_id) VALUE (?,?);",uuserId,blogId);
                    result.setCode(200);
                    result.setResult("浏览记录添加成功");
                    return result;
                } catch (DataAccessException e) {
                    e.printStackTrace();
                    result.setCode(201);
                    result.setResult("浏览记录添加失败");
                    return result;
                }
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("浏览记录查询异常");
            return result;
        }
    }

    @ApiOperation(value = "删除浏览记录",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/deleteOneLook")
    public Result deleteOneLook(@ApiParam(name = "uuserId", value = "用户ID", required = true)Integer uuserId,
                                @ApiParam(name = "blogId", value = "博客ID", required = true)Integer blogId){
        Result result = new Result();
        try {
            jdbc.update("delete from look where uuser_id = ? and blog_id = ?;",uuserId,blogId);
            result.setCode(200);
            result.setResult("删除成功");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("删除失败"+e.getMessage());
            return result;
        }

    }

    @ApiOperation(value = "删除全部浏览记录",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/deleteLook")
    public Result deleteLook(@ApiParam(name = "uuserId", value = "用户ID", required = true)Integer uuserId){
        Result result = new Result();
        try {
            jdbc.update("delete from look where uuser_id = ?;",uuserId);
            result.setCode(200);
            result.setResult("删除成功");
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("删除失败"+e.getMessage());
            return result;
        }

    }

    @ApiOperation(value = "查询赞，收藏，关注",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/userGet")
    public Result userGet(@ApiParam(name = "userId", value = "用户ID", required = true)Integer userId){
        Result result = new Result();
        try {
            List<userGet> userGets = jdbc.query("select userFAC.User_id, sum(favor) as favornum, sum(collect) as collectnum, count(follow.user_id) as fans\n" +
                    "from (select blog.User_id,favor,collect\n" +
                    "      from user_blog left join blog using (Blog_id)) as userFAC\n" +
                    "left join follow on userFAC.User_id = follow.follow_id\n" +
                    "where userFAC.User_id = ?;", new BeanPropertyRowMapper<>(userGet.class), userId);
            result.setCode(200);
            result.setResult(userGets);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("查询赞，收藏，关注异常"+e.getMessage());
            return result;
        }
    }

}
